from utils.sql_utils import SQLManager
from utils.page_utils import PageUtils

class CommentService:
    def get_comment_list(note_id):
        sql = """
            SELECT 
                rncomments.unique_id AS comment_id,
                rncomments.content AS comment_content,
                rncomments.like_count AS comment_like_count,
                rncomments.created_at AS comment_created_at,
                
                NULL AS sub_comment_id,
                NULL AS sub_comment_content,
                NULL AS sub_comment_content_belong,
                NULL AS sub_comment_like_count,
                NULL AS sub_comment_created_at,
                
                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url
            FROM 
                rb_note_comments rncomments,
                rb_admin_user rau
            WHERE 
                rncomments.note_id = %s
                AND rncomments.user_id = rau.unique_id
            UNION 
            SELECT 
                rncomments.unique_id AS comment_id,
                rncomments.content AS comment_content,
                rncomments.like_count AS comment_like_count,
                rncomments.created_at AS comment_created_at,
                
                rncscomments.unique_id AS sub_comment_id,
                rncscomments.content AS sub_comment_content,
                rncscomments.comment_id AS sub_comment_content_belong,
                rncscomments.like_count AS sub_comment_like_count,
                rncscomments.created_at AS sub_comment_created_at,
                
                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url
            FROM 
                rb_note_comments rncomments,
                rb_note_sub_comments rncscomments,
                rb_admin_user rau
            WHERE 
                rncomments.note_id = %s
                AND rncomments.unique_id = rncscomments.comment_id
                AND rncomments.user_id = rau.unique_id
            ORDER BY 
                comment_created_at ASC, 
                sub_comment_created_at ASC;

        """

        data = SQLManager().get_list(sql, (note_id,note_id))  # 获取分页数据
        
        # 分页处理
        # paginator = PageUtils(data, page=page, page_size=page_size)
        # page_data = paginator.get_page_data()
        # pagination_info = paginator.get_pagination_info()

        return data
    
    def get_comment_lists():
        sql = """
            SELECT 
                rncomments.unique_id AS comment_id,
                rncomments.content AS comment_content,
                rncomments.like_count AS comment_like_count,
                rncomments.created_at AS comment_created_at,
                rncomments.note_id AS note_id,
                
                NULL AS sub_comment_id,
                NULL AS sub_comment_content,
                NULL AS sub_comment_content_belong,
                NULL AS sub_comment_like_count,
                NULL AS sub_comment_created_at,
                
                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url
            FROM 
                rb_note_comments rncomments,
                rb_admin_user rau
            WHERE 
                rncomments.user_id = rau.unique_id
            UNION 
            SELECT 
                rncomments.unique_id AS comment_id,
                rncomments.content AS comment_content,
                rncomments.like_count AS comment_like_count,
                rncomments.created_at AS comment_created_at,
                rncomments.note_id AS note_id,
                
                rncscomments.unique_id AS sub_comment_id,
                rncscomments.content AS sub_comment_content,
                rncscomments.comment_id AS sub_comment_content_belong,
                rncscomments.like_count AS sub_comment_like_count,
                rncscomments.created_at AS sub_comment_created_at,
                
                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url
            FROM 
                rb_note_comments rncomments,
                rb_note_sub_comments rncscomments,
                rb_admin_user rau
            WHERE 
                rncomments.unique_id = rncscomments.comment_id
                AND rncomments.user_id = rau.unique_id
            ORDER BY 
                comment_created_at ASC, 
                sub_comment_created_at ASC;

        """

        data = SQLManager().get_list(sql)  # 获取分页数据

        return data
    
    def get_note_comments(note_id,current,page_size):
        # 查询主评论
        main_sql = """
        SELECT
            rncomments.unique_id AS comment_id,
            rncomments.content AS comment_content,
            rncomments.like_count AS comment_like_count,
            rncomments.created_at AS comment_created_at,
            rncomments.note_id AS note_id,
            rau.unique_id AS user_id,
            rau.admin_user_name AS user_name,
            rau.avatar_url AS user_avatar_url
        FROM
            rb_note_comments rncomments
        JOIN
            rb_admin_user rau ON rncomments.user_id = rau.unique_id
        WHERE
            rncomments.note_id = %s
        ORDER BY
            rncomments.created_at ASC;
        """

        current = current or 1
        page_size = page_size or 5

        main_comments = SQLManager().get_list_by_page(main_sql,note_id,current,page_size)
        # 查询子评论
        sub_sql = """
        SELECT
            rncscomments.unique_id AS sub_comment_id,
            rncscomments.content AS sub_comment_content,
            rncscomments.comment_id AS sub_comment_content_belong,
            rncscomments.like_count AS sub_comment_like_count,
            rncscomments.created_at AS sub_comment_created_at,
            rau.unique_id AS user_id,
            rau.admin_user_name AS user_name,
            rau.avatar_url AS user_avatar_url
        FROM
            rb_note_sub_comments rncscomments
        JOIN
            rb_admin_user rau ON rncscomments.user_id = rau.unique_id
        WHERE
            rncscomments.comment_id IN (
                SELECT unique_id FROM rb_note_comments WHERE note_id = %s
            )
        ORDER BY
            rncscomments.created_at ASC
        LIMIT %s, %s;
        """

        current = current or 1
        page_size = page_size or 5
        sub_comments = SQLManager().get_list(sub_sql,(note_id,int(current),int(page_size)))

        # 将子评论分配到对应的主评论中
        main_comment_dict = {comment['comment_id']: comment for comment in main_comments}
        for sub_comment in sub_comments:
            comment_id = sub_comment['sub_comment_content_belong']
            if comment_id in main_comment_dict:
                if 'sub_comments' not in main_comment_dict[comment_id]:
                    main_comment_dict[comment_id]['sub_comments'] = []
                main_comment_dict[comment_id]['sub_comments'].append(sub_comment)

        # 将主评论字典转换回列表
        result = list(main_comment_dict.values())
        return result
    

    def query_note_sub_comments(note_id,current,page_size):
        # 查询子评论
        sub_sql = """
        SELECT
            rncscomments.unique_id AS sub_comment_id,
            rncscomments.content AS sub_comment_content,
            rncscomments.comment_id AS sub_comment_content_belong,
            rncscomments.like_count AS sub_comment_like_count,
            rncscomments.created_at AS sub_comment_created_at,
            rau.unique_id AS user_id,
            rau.admin_user_name AS user_name,
            rau.avatar_url AS user_avatar_url
        FROM
            rb_note_sub_comments rncscomments
        JOIN
            rb_admin_user rau ON rncscomments.user_id = rau.unique_id
        WHERE
            rncscomments.comment_id IN (
                SELECT unique_id FROM rb_note_comments WHERE note_id = %s
            )
        ORDER BY
            rncscomments.created_at ASC
        LIMIT %s, %s;
        """


        current = current or 1
        page_size = page_size or 5
        sub_comments = SQLManager().get_list(sub_sql,(note_id,int(current),int(page_size)))
        return sub_comments

    def query_note_comments(note_id,current,page_size):
        # 查询主评论
        main_sql = """
        SELECT
            rncomments.unique_id AS comment_id,
            rncomments.content AS comment_content,
            rncomments.like_count AS comment_like_count,
            rncomments.created_at AS comment_created_at,
            rncomments.note_id AS note_id,
            rau.unique_id AS user_id,
            rau.admin_user_name AS user_name,
            rau.avatar_url AS user_avatar_url
        FROM
            rb_note_comments rncomments
        JOIN
            rb_admin_user rau ON rncomments.user_id = rau.unique_id
        WHERE
            rncomments.note_id = %s
        ORDER BY
            rncomments.created_at ASC
        LIMIT %s, %s;
        """

        current = current or 1
        page_size = page_size or 5
        main_comments = SQLManager().get_list(main_sql,(note_id,int(current),int(page_size)))
        return main_comments
    

    def update_note_comment_like_count(comment_id,like_count):
        sql = """
        UPDATE 
            rb_note_comments 
        SET 
            like_count = %s 
        WHERE 
            unique_id = %s;
        """
        params = (like_count + 1,comment_id)
        return SQLManager().insert(sql,params)


    